﻿/**********************************天使物流*******************************************
* 功能描述:合同管理-货主合同
* 作者:石锦涛
* 时间:2022-01-07
* 
**********************************天使物流*******************************************/
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TianShiProject.EntityFrameworkCore;
using TianShiProject.Models.Common;
using TianShiProject.Models.Contract;
using TianShiProject.Models.Contract.NewOwnerContracts;
using Volo.Abp.EntityFrameworkCore;
using Snowflake.Core;

namespace TianShiProject.Contract.NewOwnerContracts
{
    public class NewOwnerAppRepository : DapperDbContextRepository, INewOwnerAppRepository
    {
        public NewOwnerAppRepository(IDbContextProvider<TianShiProjectDbContext> dbContextProvider) : base(dbContextProvider)
        {
        }

        /// <summary>
        /// 显示 货主合同管理
        /// </summary>
        /// <param name="telet"></param>
        /// <param name="danwei"></param>
        /// <param name="principal"></param>
        /// <param name="operators"></param>
        /// <param name="dtime"></param>
        /// <param name="state"></param>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public async Task<PageResponse<NewOwnerContractDtos>> GetNewOwners(  string telet, string danwei, string principal, string operators, string dtime, int? state, int pageindex = 1, int pagesize = 5)
        {
            PageResponse<NewOwnerContractDtos> dos = new PageResponse<NewOwnerContractDtos>(); 
            try
            {
                var Sql = "select a.Id as GId,a.TheShipContractNO,a.TheContractTitle,a.TheOwnerUnit,a.FreightPrincipal,a.Line,a.TonsOfFreight,a.ConditionTonnage,a.CharterValue,a.Agent,SUBSTRING(CONVERT(Nvarchar(50),a.SignedTime),1,10) as SignedTime,a.TheContractAmount,a.ProjectDescriptio,a.ChangeTheTerms,a.ContractTheText,a.ContractStates,a.TheApprover,a.IsDelete,a.AbnormityWarning,a.FreightState,b.LineName  from NewOwnerContract a join Line b on a.Line = b.Id where a.IsDelete=0   ";

                if(!string.IsNullOrWhiteSpace(telet))
                {
                    Sql += $" and a.TheContractTitle like '%{telet}%'";
                }
                if (!string.IsNullOrWhiteSpace(danwei))
                {
                    Sql += $" and a.TheOwnerUnit like '%{danwei}%'";
                }
                if (!string.IsNullOrWhiteSpace(principal))
                {
                    Sql += $" and a.FreightPrincipal like '%{principal}%'";
                }
                if (!string.IsNullOrWhiteSpace(operators))
                {
                    Sql += $" and a.Agent like '%{operators}%'";
                }
                if (dtime!=null)
                {
                    Sql += $" and SignedTime ='{dtime}'";
                }
                if(state>0)
                {
                    Sql += $" and a.ContractStates='{state}'";
                }
                var list = await this.QueryAsync<NewOwnerContractDtos>(Sql, null);

                dos.PageTotal = list.Count();
                
                Sql += $" order by a.Id offset ({pageindex}-1)*{pagesize} rows fetch next {pagesize} rows only";
                var query = await this.QueryAsync<NewOwnerContractDtos>(Sql, null);
                dos.PageData = query;
                return  dos;

                

            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 货主合同管理-逻辑删除
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public async Task<PageResponse<NewOwnerContract>> DeletePageDelet(int Id)
        {
            try
            {
                PageResponse<NewOwnerContract> dos = new PageResponse<NewOwnerContract>();
                var sql = $"update  NewOwnerContract set IsDelete=1 where Id={Id}";
                dos.Code = await ExecuteAsync(sql,null);
                return dos;
            }
            catch (Exception)
            {

                throw;
            }
        }


        /// <summary>
        /// 修改合同数据
        /// </summary>
        /// <param name="newOwner"></param>
        /// <returns></returns>
        public async Task<PageResponse<int>> UpdateNewOwner(NewOwnerContractDtos newOwner)
        {
            try
            {
                PageResponse<int> dos = new PageResponse<int>();

                var sql = $"update NewOwnerContract set TheShipContractNO='{newOwner.TheShipContractNO}',TheContractTitle='{newOwner.TheContractTitle}',TheOwnerUnit='{newOwner.TheOwnerUnit}',FreightPrincipal='{newOwner.FreightPrincipal}',Line='{newOwner.Line}',TonsOfFreight='{newOwner.TonsOfFreight}',ConditionTonnage='{newOwner.ConditionTonnage}',CharterValue ='{newOwner.CharterValue}',Agent ='{newOwner.Agent}',SignedTime ='{newOwner.SignedTime.ToString("yyyy/MM/dd")}',TheContractAmount ='{newOwner.TheContractAmount}',ProjectDescriptio ='{newOwner.ProjectDescriptio}',ChangeTheTerms ='{newOwner.ChangeTheTerms}',ContractTheText ='{newOwner.ContractTheText}',ContractStates ='{newOwner.ContractStates}',TheApprover ='{newOwner.TheApprover}',IsDelete = '{newOwner.IsDelete}',AbnormityWarning='{newOwner.AbnormityWarning}',FreightState='{newOwner.FreightState}' where Id = '{newOwner.GId}'";
                 dos.Code = await ExecuteAsync(sql,null);
                return dos;
            }
            catch (Exception) { 
            

                throw;
            }
        }

        /// <summary>
        /// 添加货主合同
        /// </summary>
        /// <param name="newOwner"></param>
        /// <returns></returns>
        public async Task<PageResponse<int>> Postsianjia(NewOwnerContract newOwner)
        {
            try
            {
                PageResponse<int> dos = new PageResponse<int>();

                //雪花算法
                var worker = new IdWorker(1, 1);
                var id = worker.NextId().ToString();

                var sql = $"insert NewOwnerContract values  ('{id}','{newOwner.TheContractTitle}','{newOwner.TheOwnerUnit}','{newOwner.FreightPrincipal}','{newOwner.Line}','{newOwner.TonsOfFreight}','{newOwner.ConditionTonnage}', '{newOwner.CharterValue}','{newOwner.Agent}', '{newOwner.SignedTime.ToString("yyyy/MM/dd")}', '{newOwner.TheContractAmount}', '{newOwner.ProjectDescriptio}', '{newOwner.ChangeTheTerms}', '{newOwner.ContractTheText}', '{newOwner.ContractStates}', '{newOwner.TheApprover}', '{newOwner.IsDelete}','{newOwner.AbnormityWarning}','{newOwner.FreightState}')";
                dos.Code = await ExecuteAsync(sql, null);
                return dos;
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 货主批量删除
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public async Task<PageResponse<NewOwnerContractDto>> DeletEmoreNewOwner(string Id)
        {
            try
            {
                PageResponse<NewOwnerContractDto> doc = new PageResponse<NewOwnerContractDto>();
                var ids = Id.Split(',');
                foreach (var item in ids)
                {
                    var shu = Convert.ToInt32(item);
                    var list = await this.QueryAsync<NewOwnerContractDto>($"select * from NewOwnerContract where Id={shu}");
                    if (list != null)
                    {
                        var sql = $"update NewOwnerContract set IsDelete=1 where Id={shu} ";
                        doc.Code = await this.ExecuteAsync(sql, null);
                    }
                }
                return doc;
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 货主跟踪是否发货
        /// </summary>
        /// <param name="newOwner"></param>
        /// <returns></returns>
        public async Task<PageResponse<int>> UpdateFahuoNewOwner(NewOwnerContractDtos newOwner)
        {
            try
            {
                PageResponse<int> dos = new PageResponse<int>();

                var sql = $"update NewOwnerContract set TheShipContractNO='{newOwner.TheShipContractNO}',TheContractTitle='{newOwner.TheContractTitle}',TheOwnerUnit='{newOwner.TheOwnerUnit}',FreightPrincipal='{newOwner.FreightPrincipal}',Line='{newOwner.Line}',TonsOfFreight='{newOwner.TonsOfFreight}',ConditionTonnage='{newOwner.ConditionTonnage}',CharterValue ='{newOwner.CharterValue}',Agent ='{newOwner.Agent}',SignedTime ='{newOwner.SignedTime.ToString("yyyy/MM/dd")}',TheContractAmount ='{newOwner.TheContractAmount}',ProjectDescriptio ='{newOwner.ProjectDescriptio}',ChangeTheTerms ='{newOwner.ChangeTheTerms}',ContractTheText ='{newOwner.ContractTheText}',ContractStates ='{newOwner.ContractStates}',TheApprover ='{newOwner.TheApprover}',IsDelete = '{newOwner.IsDelete}',AbnormityWarning='{newOwner.AbnormityWarning}',FreightState='{newOwner.FreightState+1}' where Id = '{newOwner.GId}'";
                dos.Code = await ExecuteAsync(sql, null);
                return dos;
            }
            catch (Exception)
            {


                throw;
            }
        }
        
        /// <summary>
        /// 货主跟踪是否签收
        /// </summary>
        /// <param name="newOwner"></param>
        /// <returns></returns>
        public async Task<PageResponse<int>> UpdateQianShoNewOwner(NewOwnerContractDtos newOwner)
        {
            try
            {
                PageResponse<int> dos = new PageResponse<int>();

                var sql = $"update NewOwnerContract set TheShipContractNO='{newOwner.TheShipContractNO}',TheContractTitle='{newOwner.TheContractTitle}',TheOwnerUnit='{newOwner.TheOwnerUnit}',FreightPrincipal='{newOwner.FreightPrincipal}',Line='{newOwner.Line}',TonsOfFreight='{newOwner.TonsOfFreight}',ConditionTonnage='{newOwner.ConditionTonnage}',CharterValue ='{newOwner.CharterValue}',Agent ='{newOwner.Agent}',SignedTime ='{newOwner.SignedTime.ToString("yyyy/MM/dd")}',TheContractAmount ='{newOwner.TheContractAmount}',ProjectDescriptio ='{newOwner.ProjectDescriptio}',ChangeTheTerms ='{newOwner.ChangeTheTerms}',ContractTheText ='{newOwner.ContractTheText}',ContractStates ='{newOwner.ContractStates}',TheApprover ='{newOwner.TheApprover}',IsDelete = '{newOwner.IsDelete}',AbnormityWarning='{newOwner.AbnormityWarning=3}',FreightState='{newOwner.FreightState+1}' where Id = '{newOwner.GId}'";
                dos.Code = await ExecuteAsync(sql, null);
                return dos;
            }
            catch (Exception)
            {


                throw;
            }
        }

    }
}
